﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Text;
using System.Threading.Tasks;
using BCCommon;
using BCEntity.WeChatComponent.WeChatUserInfo;
using Dapper;

namespace BCData.WeChatComponent.WeChatUserInfo
{
    /// <summary>
    /// 微信用户信息数据处理
    /// </summary>
    public class WeChatUserInfoData : IWeChatUserInfoData
    {
        private readonly IDatabaseContext databaseContext;
        private const string QUERY = @"SELECT Openid,Unionid,Nickname,Sex,Language,City,Province,Country,Headimgurl,
                                                Subscribe,SubscribeTime,Remark,Groupid,TagidList,SubscribeScene,QrScene,
                                                QrSceneStr,CreateTime     
                                        FROM WeChatUserInfo";
        private const string QUERY_SQL = @"SELECT B.UserId,B.Phone,B.UserName,B.LastLoginTime,B.UserIntegrals,
                                                  B.Avatar,B.AccountStatus,B.ProvinceCode,B.CityCode,B.CountyCode,B.Role,
                                                  A.Openid,A.Unionid,A.Nickname,A.Sex,A.Language,A.City,A.Province,A.Country,A.Headimgurl,
                                                  A.Subscribe,A.SubscribeTime,A.Remark,A.Groupid,A.TagidList,A.SubscribeScene,A.QrScene,
                                                  A.QrSceneStr,A.CreateTime
                                            FROM WeChatUserInfo A 
                                                LEFT JOIN UserAuth C ON A.UnionId=C.UnionId
                                               LEFT JOIN User B ON C.UserId=B.UserId";

        public WeChatUserInfoData(IDatabaseContext databaseContext)
        {
            this.databaseContext=databaseContext;
        }

        public void Add(WeChatUserInfoEntity weChatUserInfoEntity)
        {
            string sql = @"INSERT INTO WeChatUserInfo(Openid,Unionid,Nickname,Sex,Language,City,Province,Country,Headimgurl,Subscribe,SubscribeTime,Remark,Groupid,TagidList,SubscribeScene,QrScene,QrSceneStr,CreateTime)
                                    VALUES(?Openid,?Unionid,?Nickname,?Sex,?Language,?City,?Province,?Country,?Headimgurl,?Subscribe,?SubscribeTime,?Remark,?Groupid,?TagidList,?SubscribeScene,?QrScene,?QrSceneStr,?CreateTime);";
            DynamicParameters parameters = new DynamicParameters();
            parameters.Add("?Openid", weChatUserInfoEntity.Openid, DbType.AnsiString, size: 50);
            parameters.Add("?Unionid", weChatUserInfoEntity.Unionid, DbType.AnsiString, size: 50);
            parameters.Add("?Nickname", weChatUserInfoEntity.Nickname, DbType.AnsiString, size: 50);
            parameters.Add("?Sex", weChatUserInfoEntity.Sex, DbType.AnsiString);
            parameters.Add("?Language", weChatUserInfoEntity.Language, DbType.AnsiString, size: 10);
            parameters.Add("?City", weChatUserInfoEntity.City, DbType.AnsiString, size: 20);
            parameters.Add("?Province", weChatUserInfoEntity.Province, DbType.AnsiString, size: 20);
            parameters.Add("?Country", weChatUserInfoEntity.Country, DbType.AnsiString, size: 20);
            parameters.Add("?Headimgurl", weChatUserInfoEntity.Headimgurl, DbType.AnsiString, size: 1000);
            parameters.Add("?Subscribe", weChatUserInfoEntity.Subscribe, DbType.Boolean);
            parameters.Add("?SubscribeTime", weChatUserInfoEntity.SubscribeTime, DbType.Int32);
            parameters.Add("?Remark", weChatUserInfoEntity.Remark, DbType.AnsiString, size: 20);
            parameters.Add("?Groupid", weChatUserInfoEntity.Groupid, DbType.Int32, size: 10);
            parameters.Add("?TagidList", weChatUserInfoEntity.TagidList, DbType.AnsiString, size: 100);
            parameters.Add("?SubscribeScene", weChatUserInfoEntity.SubscribeScene, DbType.AnsiString, size: 30);
            parameters.Add("?QrScene", weChatUserInfoEntity.QrScene, DbType.Int32,size:10);
            parameters.Add("?QrSceneStr", weChatUserInfoEntity.QrSceneStr, DbType.AnsiString, size: 100);
            parameters.Add("?CreateTime", weChatUserInfoEntity.CreateTime, DbType.DateTime);
            this.databaseContext.Master.Execute(sql, parameters, databaseContext.Transaction);
        }

        public bool CheckUserExists(string openId)
        {
            string sql = @"SELECT 1 FROM WeChatUserInfo WHERE Openid=?Openid LIMIT 1;";
            DynamicParameters parameters = new DynamicParameters();
            parameters.Add("?Openid", openId, DbType.AnsiString, size: 50);
            object result = this.databaseContext.Slave.ExecuteScalar(sql, parameters);
            return result != null;
        }

        public void Delete(string openId)
        {
            string sql = @"DELETE FROM WeChatUserInfo WHERE Openid=?Openid;";
            DynamicParameters parameters = new DynamicParameters();
            parameters.Add("?Openid", openId, DbType.AnsiString, size: 50);
            this.databaseContext.Master.ExecuteScalar(sql, parameters, databaseContext.Transaction);
        }

        public IEnumerable<WeChatUserInfoEntity> Get(List<string> openIds)
        {
            string sql = string.Format(@"{0} WHERE Openid IN ('{1}')",QUERY,string.Join("','",openIds));
            return this.databaseContext.Slave.Query<WeChatUserInfoEntity>(sql);
        }

        public WeChatUserInfoEntity GetEntity(string openId)
        {
            string sql = string.Format(@"{0} WHERE Openid=?Openid;",QUERY);
            DynamicParameters parameters = new DynamicParameters();
            parameters.Add("?Openid", openId, DbType.AnsiString, size: 50);
            return this.databaseContext.Slave.QueryEntity<WeChatUserInfoEntity>(sql, parameters);
        }

        public void Update(WeChatUserInfoEntity weChatUserInfoEntity)
        {
            string sql = @"UPDATE WeChatUserInfo SET
                                Unionid=?Unionid,
                                Nickname=?Nickname, 
                                Sex=?Sex,
                                Language=?Language,         
                                City=?City,
                                Province=?Province,       
                                Country=?Country,
                                Headimgurl=?Headimgurl, 
                                Subscribe=?Subscribe,
                                SubscribeTime=?SubscribeTime,
                                Remark=?Remark,
                                Groupid=?Groupid,   
                                TagidList=?TagidList, 
                                SubscribeScene=?SubscribeScene,
                                QrScene=?QrScene,
                                QrSceneStr=?QrSceneStr
                            WHERE Openid=?Openid;";
            DynamicParameters parameters = new DynamicParameters();
            parameters.Add("?Openid", weChatUserInfoEntity.Openid, DbType.AnsiString, size: 50);
            parameters.Add("?Unionid", weChatUserInfoEntity.Unionid, DbType.AnsiString, size: 50);
            parameters.Add("?Nickname", weChatUserInfoEntity.Nickname, DbType.AnsiString, size: 50);
            parameters.Add("?Sex", weChatUserInfoEntity.Sex, DbType.AnsiString);
            parameters.Add("?Language", weChatUserInfoEntity.Language, DbType.AnsiString, size: 10);
            parameters.Add("?City", weChatUserInfoEntity.City, DbType.AnsiString, size: 20);
            parameters.Add("?Province", weChatUserInfoEntity.Province, DbType.AnsiString, size: 20);
            parameters.Add("?Country", weChatUserInfoEntity.Country, DbType.AnsiString, size: 20);
            parameters.Add("?Headimgurl", weChatUserInfoEntity.Headimgurl, DbType.AnsiString, size: 1000);
            parameters.Add("?Subscribe", weChatUserInfoEntity.Subscribe, DbType.Boolean);
            parameters.Add("?SubscribeTime", weChatUserInfoEntity.SubscribeTime, DbType.Int32);
            parameters.Add("?Remark", weChatUserInfoEntity.Remark, DbType.AnsiString, size: 20);
            parameters.Add("?Groupid", weChatUserInfoEntity.Groupid, DbType.Int32, size: 10);
            parameters.Add("?TagidList", weChatUserInfoEntity.TagidList, DbType.AnsiString, size: 100);
            parameters.Add("?SubscribeScene", weChatUserInfoEntity.SubscribeScene, DbType.AnsiString, size: 30);
            parameters.Add("?QrScene", weChatUserInfoEntity.QrScene, DbType.Int32, size: 10);
            parameters.Add("?QrSceneStr", weChatUserInfoEntity.QrSceneStr, DbType.AnsiString, size: 100);
            this.databaseContext.Master.Execute(sql, parameters, databaseContext.Transaction);
        }

        public void AddOpenIds(string[] openIds)
        {
            StringBuilder sql = new StringBuilder(@"INSERT INTO WeChatUserInfo(Openid)VALUES");
            Parallel.ForEach(openIds, item =>
            {
                if (!this.CheckUserExists(item))
                {
                    sql.Append(string.Format("('{0}'),", item));
                }
            });
            this.databaseContext.Master.Execute(sql.ToString().TrimEnd(','),null, databaseContext.Transaction);
        }

        public IEnumerable<string> GetHundredUserOpenIds(int index)
        {
            string sql = string.Format(@"SELECT Openid FROM WeChatUserInfo LIMIT {0},100", index);
            return this.databaseContext.Slave.Query<string>(sql);
        }

        public IEnumerable<WeChatUserInfoEntity> Get(bool openUnionId,string name, DateTime? start, DateTime? end, int? tagId, string subscribeScene, string remark, string phone, string country, string province, string city, string role, bool? ascending, out int dataCount, int pageIndex = 1, int pageSize = 10)
        {
            string sql = string.Empty;
            string countSql = string.Empty;
            if (openUnionId)
            {
                sql = @"SELECT B.UserId,B.Phone,B.UserName,B.LastLoginTime,B.UserIntegrals,
                                  B.Avatar,B.AccountStatus,B.ProvinceCode,B.CityCode,B.CountyCode,B.Role,
                                  A.Openid,A.Unionid,A.Nickname,A.Sex,A.Language,A.City,A.Province,A.Country,A.Headimgurl,
                                  A.Subscribe,A.SubscribeTime,A.Remark,A.Groupid,A.TagidList,A.SubscribeScene,A.QrScene,
                                  A.QrSceneStr,A.CreateTime
                            FROM WeChatUserInfo A 
                                LEFT JOIN UserAuth C ON A.UnionId=C.UnionId
                               LEFT JOIN User B ON C.UserId=B.UserId";
                countSql = @"SELECT COUNT(1)
                            FROM WeChatUserInfo A 
                                LEFT JOIN UserAuth C ON A.UnionId=C.UnionId
                               LEFT JOIN User B ON C.UserId=B.UserId";
            }
            else
            {
                sql = @"SELECT B.UserId,B.Phone,B.UserName,B.LastLoginTime,B.UserIntegrals,
                                  B.Avatar,B.AccountStatus,B.ProvinceCode,B.CityCode,B.CountyCode,B.Role,
                                  A.Openid,A.Unionid,A.Nickname,A.Sex,A.Language,A.City,A.Province,A.Country,A.Headimgurl,
                                  A.Subscribe,A.SubscribeTime,A.Remark,A.Groupid,A.TagidList,A.SubscribeScene,A.QrScene,
                                  A.QrSceneStr,A.CreateTime
                            FROM WeChatUserInfo A 
                                LEFT JOIN UserAuth C ON A.Openid=C.UnionId
                               LEFT JOIN User B ON C.UserId=B.UserId";
                countSql = @"SELECT COUNT(1)
                            FROM WeChatUserInfo A 
                                 LEFT JOIN UserAuth C ON A.Openid=C.UnionId
                               LEFT JOIN User B ON C.UserId=B.UserId";
            }
            List<string> wheres = new List<string>();
            DynamicParameters parameters = new DynamicParameters();
            if (!string.IsNullOrWhiteSpace(name))
            {
                wheres.Add("B.UserName=?UserName");
                parameters.Add("?UserName", name, DbType.AnsiString, size: 20);
            }
            if (start.HasValue && !end.HasValue)
            {
                wheres.Add("A.CreateTime>=?Start");
                parameters.Add("?Start", start.Value, DbType.DateTime);
            }
            else if (!start.HasValue && end.HasValue)
            {
                wheres.Add("A.CreateTime<=?End");
                parameters.Add("?End", end.Value, DbType.DateTime);
            }
            else if (start.HasValue && end.HasValue)
            {
                wheres.Add("A.CreateTime>=?Start AND A.CreateTime<=?End");
                parameters.Add("?Start", start.Value, DbType.DateTime);
                parameters.Add("?End", end.Value, DbType.DateTime);
            }

            if (tagId.HasValue)
            {
                wheres.Add("FIND_IN_SET(?TagId,REPLACE(A.TagidList,' ',''))!=0");
                parameters.Add("?TagId",tagId.ToString() , DbType.AnsiString);
            }
            if (!string.IsNullOrWhiteSpace(subscribeScene))
            {
                wheres.Add("A.SubscribeScene=?SubscribeScene");
                parameters.Add("?SubscribeScene", subscribeScene, DbType.AnsiString, size: 30);
            }
            if (!string.IsNullOrWhiteSpace(remark))
            {
                wheres.Add("A.Remark=?Remark");
                parameters.Add("?Remark", remark, DbType.AnsiString, size: 20);
            }
            if (!string.IsNullOrWhiteSpace(phone))
            {
                wheres.Add("B.Phone=?Phone");
                parameters.Add("?Phone", phone, DbType.AnsiString, size: 20);
            }
            if (!string.IsNullOrWhiteSpace(country))
            {
                wheres.Add("A.Country=?Country");
                parameters.Add("?Country", country, DbType.AnsiString, size: 20);
            }
            if (!string.IsNullOrWhiteSpace(province))
            {
                wheres.Add("A.Province=?Province");
                parameters.Add("?Province", province, DbType.AnsiString, size: 20);
            }
            if (!string.IsNullOrWhiteSpace(city))
            {
                wheres.Add("A.City=?City");
                parameters.Add("?City", city, DbType.AnsiString, size: 20);
            }
            if (!string.IsNullOrWhiteSpace(role))
            {
                wheres.Add("B.Role=?Role");
                parameters.Add("?Role", role, DbType.AnsiString, size: 20);
            }
            string newSql = string.Format(@"{0} {1} ORDER BY CreateTime {4} LIMIT {2},{3}; 
                                {5} {1}",
               sql,
               wheres.Count > 0 ? " WHERE " + string.Join(" AND ", wheres) : "",
               (pageIndex == 0 ? 0 : pageIndex - 1) * pageSize,
               pageSize,
               ascending.HasValue && ascending.Value ? "ASC" : "DESC",
               countSql);

            var result = this.databaseContext.Slave.QueryMultiple<WeChatUserInfoEntity, int>(newSql, parameters);

            dataCount = result.Item2;

            return result.Item1;
        }


        public WeChatUserInfoEntity GetWechatUserByUnionId(string unionId)
        {
            string sql = string.Concat(QUERY_SQL, " WHERE A.UnionId=?UnionId AND C.AuthType='WeChat'  LIMIT 1;");
            DynamicParameters parameter = new DynamicParameters();
            parameter.Add("?UnionId", unionId.Trim(), DbType.AnsiString, size: 50);
            return this.databaseContext.Slave.QueryEntity<WeChatUserInfoEntity>(sql, parameter);
        }

        /// <summary>
        /// 绑定微信信息与用户信息
        /// </summary>
        /// <param name="user">当前登录用户</param>
        /// <param name="unionId">公众平台Id</param>
        /// <returns></returns>
        public WeChatUserInfoEntity Get(string userId, string unionId)
        {
            string sql = string.Format("{0} WHERE A.UnionId=?UnionId AND C.UserId=?UserId AND C.AuthType='WeChat';", QUERY_SQL);
            DynamicParameters parameter = new DynamicParameters();
            parameter.Add("?UserId", userId.Trim(), DbType.AnsiString, size: 50);
            parameter.Add("?UnionId", unionId.Trim(), DbType.AnsiString, size: 50);
            return this.databaseContext.Slave.QueryEntity<WeChatUserInfoEntity>(sql, parameter);
        }
        
        /// <summary>
        /// 根据UserId获取微信用户信息
        /// </summary>
        /// <param name="userId">用户Id</param>
        /// <returns></returns>
        public WeChatUserInfoEntity GetWechatUserByUserId(string userId)
        {
            string sql = string.Concat(QUERY_SQL, " WHERE C.UserId=?UserId AND C.AuthType='WeChat' LIMIT 1;");
            DynamicParameters parameter = new DynamicParameters();
            parameter.Add("?UserId", userId.Trim(), DbType.AnsiString, size: 50);
            return this.databaseContext.Slave.QueryEntity<WeChatUserInfoEntity>(sql, parameter);
        }
    }
}
